﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using DBUtility;
using MySql.Data.MySqlClient;

namespace DAL
{
	public  class DTaxiGpsHistory
	{
        public int AddList(string sql)
        {
            int rows = DbHelperMySQL.AddTaxiGpsHistoryInfo(sql, null);
            return rows;
        }

		/// <summary>
		/// 增加一条数据
		/// </summary>
		public  bool Add(Model.TaxiGpsHistoryInfo model)
		{
            StringBuilder strSql = new StringBuilder();
            strSql.Append("set names  'utf8';insert into ttaxigpshistory(");
            strSql.Append("DriverPhoneNumber,Longitude,Latitude,Updatetime,Taxistate)");
            strSql.Append(" values (");
            strSql.Append("@DriverPhoneNumber,@Longitude,@Latitude,@Updatetime,@Taxistate)");
            MySqlParameter[] parameters = {
                    new MySqlParameter("@DriverPhoneNumber", MySqlDbType.VarChar,15),
                    new MySqlParameter("@Longitude", MySqlDbType.Double),
                    new MySqlParameter("@Latitude", MySqlDbType.Double),
                    new MySqlParameter("@Updatetime", MySqlDbType.DateTime),
                    new MySqlParameter("@Taxistate", MySqlDbType.Int32)};
            parameters[0].Value = model.DriverPhoneNumber;
            parameters[1].Value = model.Longitude;
            parameters[2].Value = model.Latitude;
            parameters[3].Value = model.Time;
            parameters[4].Value = model.Taxistate;
           
            int rows =DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		#region  Method

		/// <summary>
		/// 得到最大ID
		/// </summary>
		public  int GetMaxId()
		{
			return DbHelperMySQL.GetMaxID("id", "ttaxigpshistory");
		}

		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public  bool Exists(int id)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select count(1) from ttaxigpshistory");
			strSql.Append(" where id=@id");
			MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)
};
			parameters[0].Value = id;

			return DbHelperMySQL.Exists(strSql.ToString(), parameters);
		}


	
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public  bool Update(Model.TaxiGpsHistoryInfo model)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("update ttaxigpshistory set ");
			strSql.Append("DriverPhoneNumber=@DriverPhoneNumber,");
			strSql.Append("Longitude=@Longitude,");
			strSql.Append("Latitude=@Latitude,");
			strSql.Append("Updatetime=@Updatetime,");
			strSql.Append("Taxistate=@Taxistate,");
			strSql.Append(" where id=@id");
			MySqlParameter[] parameters = {
					new MySqlParameter("@DriverPhoneNumber", MySqlDbType.VarChar,15),
					new MySqlParameter("@Longitude", MySqlDbType.Double),
					new MySqlParameter("@Latitude", MySqlDbType.Double),
					new MySqlParameter("@Updatetime", MySqlDbType.DateTime),
					new MySqlParameter("@Taxistate", MySqlDbType.VarChar,10),
					new MySqlParameter("@id", MySqlDbType.Int32,10)};
			parameters[0].Value = model.DriverPhoneNumber;
			parameters[1].Value = model.Longitude;
			parameters[2].Value = model.Latitude;
			parameters[3].Value = model.Time;
			parameters[4].Value = model.Taxistate;
			parameters[6].Value = model.Id;

			int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public  bool Delete(int id)
		{

			StringBuilder strSql = new StringBuilder();
			strSql.Append("delete from ttaxigpshistory ");
			strSql.Append(" where id=@id");
			MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)
};
			parameters[0].Value = id;

			int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public  bool DeleteList(string idlist)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("delete from ttaxigpshistory ");
			strSql.Append(" where id in (" + idlist + ")  ");
			int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public  Model.TaxiGpsHistoryInfo GetModel(int id)
		{

			StringBuilder strSql = new StringBuilder();
			strSql.Append("select id,DriverPhoneNumber,Longitude,Latitude,Updatetime,Taxistate,image from ttaxigpshistory ");
			strSql.Append(" where id=@id");
			MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)
};
			parameters[0].Value = id;

			Model.TaxiGpsHistoryInfo model = new Model.TaxiGpsHistoryInfo();
			DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
			if (ds.Tables[0].Rows.Count > 0)
			{
				if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
				{
					model.Id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
				}
				if (ds.Tables[0].Rows[0]["DriverPhoneNumber"] != null && ds.Tables[0].Rows[0]["DriverPhoneNumber"].ToString() != "")
				{
					model.DriverPhoneNumber = ds.Tables[0].Rows[0]["DriverPhoneNumber"].ToString();
				}
				if (ds.Tables[0].Rows[0]["Longitude"] != null && ds.Tables[0].Rows[0]["Longitude"].ToString() != "")
				{
					//model.Longitude=ds.Tables[0].Rows[0]["Longitude"].ToString();
				}
				if (ds.Tables[0].Rows[0]["Latitude"] != null && ds.Tables[0].Rows[0]["Latitude"].ToString() != "")
				{
					//model.Latitude=ds.Tables[0].Rows[0]["Latitude"].ToString();
				}
				if (ds.Tables[0].Rows[0]["Updatetime"] != null && ds.Tables[0].Rows[0]["Updatetime"].ToString() != "")
				{
					model.Time = DateTime.Parse(ds.Tables[0].Rows[0]["Updatetime"].ToString());
				}
				if (ds.Tables[0].Rows[0]["Taxistate"] != null && ds.Tables[0].Rows[0]["Taxistate"].ToString() != "")
				{
					model.Taxistate = byte.Parse(ds.Tables[0].Rows[0]["Taxistate"].ToString());
				}
			 
				return model;
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public  DataSet GetList(string strWhere)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select id,DriverPhoneNumber,Longitude,Latitude,Updatetime,Taxistate,image ");
			strSql.Append(" FROM ttaxigpshistory ");
			if (strWhere.Trim() != "")
			{
				strSql.Append(" where " + strWhere);
			}
			return DbHelperMySQL.Query(strSql.ToString());
		}

        /// <summary>
        /// 获得数据列表
        /// </summary>
        //public DataSet GetList(string driverPhoneNumber, DateTime day)
        //{

        //    string bb = day.ToShortDateString();

        //    DateTime d1 = DateTime.Parse(bb);

        //    DateTime d2 = d1.AddDays(1);

        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("select id,DriverPhoneNumber,Longitude,Latitude,Updatetime,Taxistate ");
        //    strSql.Append(" FROM ttaxigpshistory ");
        //    strSql.Append(" Where DriverPhoneNumber='");
        //    strSql.Append(driverPhoneNumber);
        //    strSql.Append("' and Updatetime between '");
        //    strSql.Append(d1.ToString("yyyy-MM-dd"));
        //    strSql.Append("' and '");
        //    strSql.Append(d2.ToString("yyyy-MM-dd"));
        //    strSql.Append("'");

        //    return DbHelperMySQL.Query(strSql.ToString());
        //}
        public DataSet GetList(string driverPhoneNumber, DateTime startTime,DateTime endTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,DriverPhoneNumber,Longitude,Latitude,Updatetime,Taxistate ");
            strSql.Append(" FROM ttaxigpshistory ");
            strSql.Append(" Where DriverPhoneNumber='");
            strSql.Append(driverPhoneNumber);
            strSql.Append("' and Updatetime between '");
            strSql.Append(startTime);
            strSql.Append("' and '");
            strSql.Append(endTime);
            strSql.Append("'order by Updatetime asc");

            return DbHelperMySQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取各个载客率、空载率统计记录 
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>  
        public DataSet GetStatisticsList(string strWhere)
        {
            //0-空载，1-预约，2-载客，3-交班，4-可拼车   空载率：0、4 载客率：2，其他：1、3
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("(select TaxiNo from ttaxi where id=");
            strSql.Append("(select TaxiId from tdriver where PhoneNumber = DriverPhoneNumber)) as TaxiNo,");
            strSql.Append("(count(case Taxistate when '0' then DriverPhoneNumber end)");
            strSql.Append("+count(case Taxistate when '4' then DriverPhoneNumber end))");
            strSql.Append("/count(*)*100 as NoPsgPerts,");
            strSql.Append("count(case Taxistate when '2' then DriverPhoneNumber end)/count(*)*100 ");
            strSql.Append("as CarryPsgPerts,");
            strSql.Append("(count(case Taxistate when '1' then DriverPhoneNumber end)");
            strSql.Append("+count(case Taxistate when '3' then DriverPhoneNumber end))");
            strSql.Append("/count(*)*100 as OtherPerts");           
            strSql.Append(" from ttaxigpshistory t where ");
            strSql.Append(strWhere);
            strSql.Append(" group by DriverPhoneNumber");          
            strSql.Append(" order by NoPsgPerts asc");
            return DbHelperMySQL.Query(strSql.ToString());
        }
       
        /// <summary>
        /// 返回一个载客率、空载、其它率结果的数组
        /// </summary>
        /// <param name="intObject">统计对象:1表示单辆车，2表示分组，3表示所有车</param>
        /// <param name="strValue">对象值</param>
        /// <param name="strWhere">条件</param>  
        /// <param name="dateType">时间类型：1：小时，2：天，3：月，4：年</param>
        /// <returns></returns>
        public DataSet GetStatisResult(int intObject, string strValue, 
            int dateType,string strWhere)
        {           
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select (count(case Taxistate when '0' ");
            strSql.Append("then DriverPhoneNumber end) +");
            strSql.Append("count(case Taxistate when '4' then DriverPhoneNumber end))");
            strSql.Append("/count(*)*100 as NoPsgPerts,");
            strSql.Append("count(case Taxistate when '2' then DriverPhoneNumber end)");
            strSql.Append("/count(*)*100 as  CarryPsgPerts,");
            strSql.Append("(count(case Taxistate when '1' then DriverPhoneNumber end)");
            strSql.Append("+count(case Taxistate when '3' then DriverPhoneNumber end))");
            strSql.Append("/count(*)*100 as OtherPerts");
            if(dateType!=0)
            {
               switch(dateType)
               {
                   case 1:  //按小时分组
                       //strSql.Append(",DATE_FORMAT(Updatetime, '%H' ) AS DT ");                                              
                       strSql.Append(",DATE_FORMAT(Updatetime, '%H点' ) AS DT ");                                              
                       break;
                   case 2: //按天分组
                       //strSql.Append(",DATE_FORMAT(Updatetime, '%Y-%m-%d' ) AS DT ");
                       strSql.Append(",DATE_FORMAT(Updatetime, '%d' ) AS DT ");                     
                       break;
                   case 3:  //按月分组
                       //strSql.Append(",DATE_FORMAT(Updatetime, '%Y-%m' ) AS DT ");
                       strSql.Append(",DATE_FORMAT(Updatetime, '%m月' ) AS DT ");                                     
                       break;
                   case 4: //按年分组
                       strSql.Append(",DATE_FORMAT(Updatetime, '%Y年' ) AS DT ");                       
                       break;
               }

            }
            strSql.Append(" from ttaxigpshistory ");
            if(intObject==1)
            {
                strSql.Append("where DriverPhoneNumber = '");
                strSql.Append(strValue);
                strSql.Append("' ");
            }
            if(intObject==2)
            {
                strSql.Append("where DriverPhoneNumber in ");
                strSql.Append("(select PhoneNumber from tDriver where TaxiId in");
                strSql.Append("(select id from tTaxi where TaxiGroupid='");
                strSql.Append(strValue);
                strSql.Append("')) ");                
            }
            //if(intObject==3 && strWhere.Trim()!="")
            //{
            //    strSql.Append("Where ");                
            //}
            if(strWhere.Trim()!="")
            {
                if(intObject!=3)
                {
                    strSql.Append(" and ");                   
                }                
                else
                {
                    strSql.Append("Where ");                
                }
                strSql.Append(strWhere);    
            }
            if (dateType!=0)
            {
                strSql.Append(" group by DT order by DT asc");
            }
           return  DbHelperMySQL.Query(strSql.ToString());
            
        }

        /// <summary>
        /// 出租车在线数量统计-按日期统计
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataSet GetTaxiOnlineCountReport(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT count(distinct DriverPhoneNumber) as DrCount,");
            strSql.Append("DATE_FORMAT(Updatetime, '%m-%d' ) AS DT ");
            strSql.Append("FROM ttaxigpshistory t ");
            if(strWhere.Trim()!="")
            {
                strSql.Append("where ");
                strSql.Append(strWhere);
            }            
            strSql.Append(" group by DT;");
            return DbHelperMySQL.Query(strSql.ToString());
        }
        

		/// <summary>
		/// 获取记录总数
		/// </summary>
		public  int GetRecordCount(string strWhere)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("select count(1) FROM ttaxigpshistory ");
			if (strWhere.Trim() != "")
			{
				strSql.Append(" where " + strWhere);
			}
			//object obj = DbHelperSQL.GetSingle(strSql.ToString());
		    DataSet ds = DbHelperMySQL.Query(strSql.ToString());
		    if (ds.Tables.Count>0 && ds.Tables[0].Rows.Count>0)
		    {
		        int count =Convert.ToInt32(ds.Tables[0].Rows[0][0]) ;
		        return count;
		    }		    
		        return 0;		   		
		}
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public  DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("SELECT * FROM ( ");
			strSql.Append(" SELECT ROW_NUMBER() OVER (");
			if (!string.IsNullOrEmpty(orderby.Trim()))
			{
				strSql.Append("order by T." + orderby);
			}
			else
			{
				strSql.Append("order by T.id desc");
			}
			strSql.Append(")AS Row, T.*  from ttaxigpshistory T ");
			if (!string.IsNullOrEmpty(strWhere.Trim()))
			{
				strSql.Append(" WHERE " + strWhere);
			}
			strSql.Append(" ) TT");
			strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
			return DbHelperMySQL.Query(strSql.ToString());
		}

		/*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			MySqlParameter[] parameters = {
					new MySqlParameter("@tblName", MySqlDbType.VarChar, 255),
					new MySqlParameter("@fldName", MySqlDbType.VarChar, 255),
					new MySqlParameter("@PageSize", MySqlDbType.Int32),
					new MySqlParameter("@PageIndex", MySqlDbType.Int32),
					new MySqlParameter("@IsReCount", MySqlDbType.Bit),
					new MySqlParameter("@OrderType", MySqlDbType.Bit),
					new MySqlParameter("@strWhere", MySqlDbType.VarChar,1000),
					};
			parameters[0].Value = "ttaxigpshistory";
			parameters[1].Value = "id";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperMySQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

		#endregion  Method
	}
}
